﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace DBAccess.DAL
{
    public class DatabaseClass
    {
          private MySqlConnection _mysqlConnection = new MySqlConnection();
        private MySqlCommand _mysqlCommand = new MySqlCommand();
        private MySqlDataAdapter _mysqlDadapter;
        private MySqlDataReader _mysqlDataReader;

        public string strDataString = "";

        //
        public DatabaseClass() {
            try
            {
                this.strDataString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringModel"].ToString();
            }
            catch
            {
                //this.strDataString = System.Configuration.ConfigurationManager.AppSettings["ConnectionStringModel"];
            }
        }
        //'Khởi tạo Object
        public DatabaseClass(string ConnectString)
        {
            this.strDataString = ConnectString;
        }

        public void ConnectData(){
            if (_mysqlConnection == null) _mysqlConnection = new MySqlConnection();
            if (_mysqlConnection.State == System.Data.ConnectionState.Closed)
            {
                _mysqlConnection.ConnectionString = strDataString;
                _mysqlConnection.Open();
            }
            if (_mysqlCommand == null) _mysqlCommand = new MySqlCommand();
            if (_mysqlDadapter == null) _mysqlDadapter = new MySqlDataAdapter();
            _mysqlCommand.Connection = _mysqlConnection;
            _mysqlCommand.CommandTimeout = 120;
            _mysqlCommand.CommandType = System.Data.CommandType.Text;

        }

        //Đóng kết nối dữ liệu
        public void CloseData() { 
            if (_mysqlDataReader != null) _mysqlDataReader.Close();
            if (_mysqlConnection != null)
            {
                if (_mysqlConnection.State == System.Data.ConnectionState.Open) _mysqlConnection.Close();
            }
            _mysqlDataReader = null;
            _mysqlConnection = null;
        }

        //Chạy câu lệnh ExecuteNonQuery (không trả về dữ liệu)
        public void ExeNonQuery(string strQuery)
        {
            if (_mysqlConnection == null) ConnectData();
            if (_mysqlDataReader != null) _mysqlDataReader.Close();
            _mysqlCommand.CommandText = strQuery;
            _mysqlCommand.ExecuteNonQuery();
        }

        //Chạy câu lệnh ExecuteReader (có hoặc không trả về dữ liệu - lưu ý đóng dữ liệu)
        public MySqlDataReader ExeReader(string strQuery)
        {
            if (_mysqlConnection == null) ConnectData();
            if (_mysqlDataReader != null) _mysqlDataReader.Close();
            _mysqlCommand.CommandText = strQuery;
            _mysqlDataReader = _mysqlCommand.ExecuteReader();
            return _mysqlDataReader;
        }

        public DataSet ExeDataset(string strQuery)
        {
            if (_mysqlConnection == null) ConnectData();
            if (_mysqlDataReader != null) _mysqlDataReader.Close();
            _mysqlCommand.CommandText = strQuery;
            _mysqlDadapter.SelectCommand = _mysqlCommand;
            DataSet ds = new DataSet();
            _mysqlDadapter.Fill(ds);
            return ds;
        }


        public MySqlCommand GetCommand(string sql, MySqlParameter[] pa, bool hasStore)
        {
            _mysqlConnection = new MySqlConnection(strDataString);
            if (_mysqlConnection.State != ConnectionState.Open)
            {
                _mysqlConnection.Open();
            }
            _mysqlCommand = new MySqlCommand(sql, _mysqlConnection);
            if (hasStore)
            {
                _mysqlCommand.CommandType = CommandType.StoredProcedure;
            }
            AddParameter(_mysqlCommand, pa);
            return _mysqlCommand;
        }
        public MySqlCommand GetCommandNonParameter(string sql, bool hasStore)
        {
            _mysqlConnection = new MySqlConnection(strDataString);
            if (_mysqlConnection.State != ConnectionState.Open)
            {
                _mysqlConnection.Open();
            }
            _mysqlCommand = new MySqlCommand(sql, _mysqlConnection);
            if (hasStore)
            {
                _mysqlCommand.CommandType = CommandType.StoredProcedure;
            }

            return _mysqlCommand;
        }

        private void AddParameter(MySqlCommand command, MySqlParameter[] listPa)
        {
            foreach (MySqlParameter pa in listPa)
            {
                command.Parameters.Add(pa);
            }
        }
    }
}
